from xlrd import open_workbook
from xlwt import Workbook, Font, Borders, Alignment, Pattern, XFStyle


class MyExcelHelper():
    def __init__(self):
        self.path = ''
        self.sheet_name = ''
        self.encoding = 'utf-8'

    def get_sheet(self):
        bk = open_workbook(self.path)  # bk是Book类的对象
        sh = bk.sheet_by_name(self.sheet_name)  # sh是Sheet类的对象
        return sh

    def read_by_row(self, start_row, start_col, end_col=None):
        sh = self.get_sheet()
        row_values = sh.row_values(start_row, start_col, end_col)
        return row_values

    def read_by_col(self, start_col, start_row, end_row=None):
        sh = self.get_sheet()
        col_values = sh.col_values(start_col, start_row, end_row)
        return col_values

    def read_by_cell(self, row, col):
        sh = self.get_sheet()
        cell_value = sh.cell_value(row, col)
        return cell_value

    def read_list(self, start_row, end_row, start_col, end_col):
        sh = self.get_sheet()
        data = []
        for i in range(start_row, end_row):
            data.append(sh.row_values(i, start_col, end_col))
        return data

    def write(self, title, content, start_row, start_col):
        font = Font()
        font.bold = True

        borders = Borders()
        borders.left = borders.THIN
        borders.right = borders.THIN
        borders.top = borders.THIN
        borders.bottom = borders.THIN

        alignment = Alignment()
        alignment.horz = alignment.HORZ_CENTER

        pattern = Pattern()
        pattern.pattern = pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = 7

        title_style = XFStyle()
        title_style.font = font
        title_style.borders = borders
        title_style.alignment = alignment
        title_style.pattern = pattern

        content_style = XFStyle()
        content_style.borders = borders
        content_style.alignment = alignment

        wb = Workbook(self.encoding)
        sheet = wb.add_sheet(self.sheet_name)
        for i in range(len(title)):
            sheet.write(start_row, start_col + i, title[i], title_style)

        for i in range(len(content)):
            for j in range(len(content[i])):
                sheet.write(start_row + 1 + i, start_col + j, content[i][j], content_style)

        wb.save(self.path)


# xls = MyExcelHelper()
# xls.path = r'd:\doc\博思创2.xls'
# xls.sheet_name = '员工表'
# data = xls.read_by_row(12,5)
# print(data)
#
# data = xls.read_by_col(5,6)
# print(data)
#
# data = xls.read_by_cell(9,6)
# print(data)

# data = xls.read_list(9,15,5,7)
# print(data)
# title = ['部门编号', '部门名称', '部门地址']
# content = [
#     [1, '研发部', '三楼'],
#     [2, '测试部', '顶楼'],
#     [3, '运维部', '负一楼']
# ]
# xls.write(title, content, 10, 10)
